package com.zhucai.credit.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.zhucai.credit.model.OrderList;

public interface OrderListRepository extends
		PagingAndSortingRepository<OrderList, Long>,
		JpaSpecificationExecutor<OrderList> {
	
	
	public List<OrderList> findByContractIdAndStatusNot(Long contractId,Integer status);

	@Query(value="select tbo.* from tb_xa_orderlist tbo where tbo.id =?1 and tbo.status <> ?2",nativeQuery=true)
	public OrderList findByOrderIdAndStatusNot(Long modelId, int delete);

	@Query(value="select tbo.* from tb_xa_orderlist tbo where tbo.contract_id =?1 and tbo.status <> 3 and tbo.order_status =?2",nativeQuery=true)
	public List<OrderList> findByContractIdAndOrderStatus(Long gettId,Integer inDispute);
	
	
	public List<OrderList> findByContractIdAndOrderStatusAndStatusNot(Long contractId,Integer orderStatus,Integer status);

	
	
	@Query(value = "select txol.id,txol.contract_id,txcl.contract_num_zhucai,txcl.purchase_intro,txcl.purchase_project_name," 
			+ "txcl.supplier_name,txcl.money_estimated,txol.actual_amount_paid,txol.amount,"
			+   "txol.type,txcl.purchaser_name, txol.payment_date,txol.order_status " +
			"from tb_xa_orderlist txol "+
			"LEFT JOIN tb_xa_contract_list txcl on txol.contract_id=txcl.id  "
			+ "where txol.order_status in ?1 and txcl.status <> 3 " 
			+ "and  (txcl.create_user in (?2) or txcl.process_user_ids like concat('%{',?8,'}%') or txcl.contract_run_approver = ?8 ) "
			+ "and ((?3 is null or txcl.contract_num_self like concat('%',?3,'%')) "
			+ "or (?4 is null or txcl.purchase_intro like concat('%',?4,'%')) "
			+ "or (?5 is null or txcl.purchase_project_name like concat('%',?5,'%')))"
			+ "order by txol.complete_date desc limit ?6,?7",nativeQuery=true)
	public List<Object[]> findOrderListPageByPurchaserByCondition(List<Integer> status,List<Long> createUserIds,String contractCode,
			String bidContent, String projectName,Integer fromIndex,
			Integer endIndex,Long currentUserId);
	    
	    
    @Query(value = "select count(txol.id) from tb_xa_orderlist txol "+
    		"LEFT JOIN tb_xa_contract_list txcl on txol.contract_id=txcl.id  "+
    		"where txol.order_status in ?1 and txcl.status <> 3 " 
    		+ "and (txcl.create_user in (?2) or txcl.process_user_ids like concat('%{',?6,'}%') or txcl.contract_run_approver = ?6) "
    		+ "and ((?3 is null or txcl.contract_num_self like concat('%',?3,'%')) "
			+ "or (?4 is null or txcl.purchase_intro like concat('%',?4,'%')) "
			+ "or (?5 is null or txcl.purchase_project_name like concat('%',?5,'%'))) ",nativeQuery=true)
    public Integer findOrderListCountByPurchaserByCondition(List<Integer> status,List<Long> buyerUserIds,String contractCode,
			String bidContent, String projectName,Long currentUserId);
    
    @Query(value = "select txol.id,txol.contract_id,txcl.contract_num_zhucai,txcl.purchase_intro,txcl.purchase_project_name," 
			+ "txcl.supplier_name,txcl.money_estimated,txol.actual_amount_paid,txol.amount,"
			+   "txol.type,txcl.purchaser_name, txol.payment_date,txol.order_status " +
    		"from tb_xa_orderlist txol "+
    		"LEFT JOIN tb_xa_contract_list txcl on txol.contract_id=txcl.id  "+
    		"where txol.order_status in ?1 and txcl.status <> 3  and (txcl.process_user_ids like concat('%{',?2,'}%') or txcl.supplier_sales_id in (?8))  "
    		+ "and ((?3 is null or txcl.contract_num_self like concat('%',?3,'%')) "
			+ "or (?4 is null or txcl.purchase_intro like concat('%',?4,'%')) "
			+ "or (?5 is null or txcl.purchase_project_name like concat('%',?5,'%'))) "
			+ "order by txol.complete_date desc limit ?6,?7",nativeQuery=true)
    public List<Object[]> findUnConfirmPayPlan(List<Integer> status,Long supplierId,String contractCode,
			String bidContent, String projectName,Integer fromIndex,Integer endIndex,List<Long> supplierIds);
    
    @Query(value = "select count(txol.id) from tb_xa_orderlist txol "+
    		"LEFT JOIN tb_xa_contract_list txcl on txol.contract_id=txcl.id  "+
    		"where txol.order_status in ?1 and txcl.status <> 3 and (txcl.process_user_ids like concat('%{',?2,'}%') or txcl.supplier_sales_id in (?6)) "
    		+ "and ((?3 is null or txcl.contract_num_self like concat('%',?3,'%')) "
			+ "or (?4 is null or txcl.purchase_intro like concat('%',?4,'%')) "
			+ "or (?5 is null or txcl.purchase_project_name like concat('%',?5,'%'))) ",nativeQuery=true)
    public Integer findUnConfirmPayPlanCount(List<Integer> status,Long supplierId,String contractCode,
			String bidContent, String projectName,List<Long> supplierIds);
	
    
    @Query(value = " SELECT SUM(a.actual_amount_paid) payment_money FROM tb_xa_orderlist a where a.contract_id = ?1 and a.order_status >= ?2  ",nativeQuery = true)
	public Object findPaymentMoneyByContractIdAndStatus(Long contractId,Integer checked);

    @Query(value="select txo.* from tb_xa_orderlist txo where txo.id = ?1 and txo.status <> ?2",nativeQuery=true)
	public OrderList findBytIdAndStatusNot(Long bizId, int delete);
    
    @Query(value = "select count(*) from tb_xa_orderlist txo where txo.contract_id=?1 and txo.order_status <> 6  and txo.status<>3",nativeQuery = true)
	public Integer findOrderCountByUnfinished(Long contractId);





}
